package mwa.cs.dao;
import java.util.List;
import java.util.Map;
import mwa.cs.database.Database;
import mwa.cs.model.RcTransM;
import mwa.cs.util.JDate;

public class RcTransMDAO {

    /** a database of this RC_TRANS_M */
    private Database db;

    /**
     * Instantiate a new object of this class
     * @param db a database
     */
    public RcTransMDAO(Database db) {
        this.db = db;
    }

    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM rc_trans_m";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public void insertRcTransM(RcTransM poRcTansM) {
        String vaSql = "INSERT INTO rc_trans_m  (site_id, serial_number, account, payer_name, payer_address, net_amount, gross_amount, receive_date,"
        		+ " receive_time, vat_amount, status, insert_user_id, insert_date, insert_time, penalty, receive_employee)  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
        try {
            db.insert(vaSql, poRcTansM.getVnSiteId()
            		,poRcTansM.getVnSerialNumber()
            		,poRcTansM.getVaAccount()
            		,poRcTansM.getVaPayerName()
            		,poRcTansM.getVaPayerAddress()
            		,poRcTansM.getVnNetAmount()
            		,poRcTansM.getVnGrossAmount()
            		,poRcTansM.getVaReceiveDate()
            		,poRcTansM.getVaReceiveTime()
            		,poRcTansM.getVnVatAmount()
            		,poRcTansM.getVaStatus()
            		,poRcTansM.getVnInsertUserId()
            		,poRcTansM.getVaInsertDate()
            		,poRcTansM.getVaInsertTime()
            		,poRcTansM.getVnPenalty()
            		,poRcTansM.getVnReceiveEmployee());
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    
    public List<Map<String, Object>> findRcTransMByRecieveEmployee(int pnSiteId) {
    	String vaCurrentDate = JDate.getYear()+JDate.getMonth()+JDate.getDay();
        String vaSql = "SELECT rcm.receive_employee, usl.user_pname||' '||usl.user_fname||' '||usl.user_lname AS EMP_NAME,rcm.receive_employee AS RECEIVE_EMPLOYEE,"
        		+ " SUM(rcm.gross_amount) AS EMP_GROSS , SUM(rcm.penalty) AS RC_PENALTY FROM rc_trans_m rcm "
        		+ "JOIN user_login usl ON rcm.receive_employee = usl.user_id WHERE rcm.SITE_ID = ? AND rcm.close_date = ? AND NVL(rcm.status,0) NOT IN 'C' GROUP BY rcm.RECEIVE_EMPLOYEE,"
        		+ " usl.user_id,usl.user_pname,usl.user_fname,usl.user_lname";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, vaCurrentDate);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> findRcTransMByRecieveEmployeeNull(int pnSiteId) {
    	String vaCurrentDate = JDate.getYear()+JDate.getMonth()+JDate.getDay();
        String vaSql = "SELECT rcm.receive_employee, usl.user_pname||' '||usl.user_fname||' '||usl.user_lname AS EMP_NAME,rcm.receive_employee AS RECEIVE_EMPLOYEE,"
        		+ " SUM(rcm.gross_amount) AS EMP_GROSS , SUM(rcm.penalty) AS RC_PENALTY FROM rc_trans_m rcm "
        		+ "JOIN user_login usl ON rcm.receive_employee = usl.user_id WHERE rcm.SITE_ID = ? AND rcm.receive_employee NOT IN "
        		+ " (SELECT RECEIVE_EMPLOYEE FROM RC_TRANS_M WHERE CLOSE_DATE = ?) AND rcm.close_date is null  AND NVL(rcm.status,0) NOT IN 'C' GROUP BY rcm.RECEIVE_EMPLOYEE, usl.user_id, "
        		+ "usl.user_pname,usl.user_fname,usl.user_lname, usl.user_id,usl.user_pname,usl.user_fname,usl.user_lname";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, vaCurrentDate);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public double sumPenaltyByReceiveEmployee(int pnSiteId, int pnEmployee) {
    	double vnTotalPenalty = 0.0;
    	String vaSql = "SELECT SUM(penalty) AS TOTAL_PENALTY FROM rc_trans_m WHERE site_id = ? AND RECEIVE_EMPLOYEE = ? AND close_date IS null AND NVL(status,0) NOT IN 'C' ";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnEmployee);
    	try {
	       	vnTotalPenalty = Double.parseDouble( result.get("TOTAL_PENALTY").toString());
		} catch (Exception e) {
			e.getMessage();
		}
    	return vnTotalPenalty;
    }
    
    public double sumPenaltyForCancelByReceiveEmployee(int pnSiteId, int pnEmployee) {
    	double vnTotalPenalty = 0.0;
    	String vaCurrentDate = JDate.getYear()+JDate.getMonth()+JDate.getDay();
    	String vaSql = "SELECT SUM(penalty) AS TOTAL_PENALTY FROM rc_trans_m WHERE site_id = ? AND RECEIVE_EMPLOYEE = ? AND close_date = ? AND NVL(status,0) NOT IN 'C' ";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnEmployee, vaCurrentDate);
    	try {
	       	vnTotalPenalty = Double.parseDouble( result.get("TOTAL_PENALTY").toString());
		} catch (Exception e) {
			e.getMessage();
		}
    	return vnTotalPenalty;
    }
    
    public List<Map<String, Object>> sumVatAmountAndBillAmount(int pnSiteId, int pnEmployee) {
        String vaSql = "SELECT rtd.receive_code, SUM(rtd.VAT_AMOUNT) AS VAT_TOTAL, SUM( rtd.bill_amount) AS BILL_TOTAL FROM rc_trans_d rtd JOIN rc_trans_m rtm  "
        		+ " ON rtd.serial_number = rtm.serial_number WHERE rtm.site_id = ? AND rtm.receive_employee = ? AND NVL(rtm.status,0) NOT IN 'C' "
        		+ " AND rtd.receive_code <> 21 AND rtm.close_date is null "
        		+ " GROUP BY rtd.receive_code";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnEmployee);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> sumVatAmountAndBillAmountReceiveCodeIs21(int pnSiteId, int pnEmployee) {
        String vaSql = "SELECT rtd.receive_code, SUM(rtd.VAT_AMOUNT) AS VAT_TOTAL, SUM(rtd.water_charge + WATER_FEE) "
        		+ " AS CHARGE_AND_FEE, SUM(rtd.garbage_charge) AS GARGEBAGE, SUM(rtd.wastewater_charge) AS WASTEWATER, SUM(rtd.other_charge)AS OTHER_CHARGE "
        		+ " FROM rc_trans_d rtd JOIN rc_trans_m rtm ON rtd.serial_number = rtm.serial_number WHERE rtm.site_id = ? AND rtm.receive_employee = ? "
        		+ " AND rtd.receive_code = 21 AND NVL(rtm.status,0) NOT IN 'C' AND rtm.close_date is null GROUP BY rtd.receive_code";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnEmployee);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> sumVatAmountAndBillAmountIsCurrentDate(int pnSiteId, int pnEmployee) {
    	String vaCurrentDate = JDate.getYear()+JDate.getMonth()+JDate.getDay();
        String vaSql = "SELECT rtd.receive_code, SUM(rtd.VAT_AMOUNT) AS VAT_TOTAL, SUM( rtd.bill_amount) AS BILL_TOTAL FROM rc_trans_d rtd JOIN rc_trans_m rtm  "
        		+ " ON rtd.serial_number = rtm.serial_number WHERE rtm.site_id = ? AND rtm.receive_employee = ? AND NVL(rtm.status,0) NOT IN 'C' AND rtd.receive_code <> 21 AND rtm.close_date = ? "
        		+ " GROUP BY rtd.receive_code";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnEmployee, vaCurrentDate);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> sumVatAmountAndBillAmountReceiveCodeIs21IsCurrentDate(int pnSiteId, int pnEmployee) {
    	String vaCurrentDate = JDate.getYear()+JDate.getMonth()+JDate.getDay();
        String vaSql = "SELECT rtd.receive_code, SUM(rtd.VAT_AMOUNT) AS VAT_TOTAL, SUM(rtd.water_charge + WATER_FEE) "
        		+ " AS CHARGE_AND_FEE, SUM(rtd.garbage_charge) AS GARGEBAGE, SUM(rtd.wastewater_charge) AS WASTEWATER, SUM(rtd.other_charge)AS OTHER_CHARGE "
        		+ " FROM rc_trans_d rtd JOIN rc_trans_m rtm ON rtd.serial_number = rtm.serial_number WHERE rtm.site_id = ? AND rtm.receive_employee = ? "
        		+ " AND rtd.receive_code = 21 AND rtm.close_date = ? AND NVL(rtm.status,0) NOT IN 'C' GROUP BY rtd.receive_code";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnEmployee, vaCurrentDate);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public int findExistTransactionData(int pnSiteId, int pnEmployee) {
    	String vaSql = "SELECT count(*) as SIZE_DATA FROM rc_trans_m WHERE site_id = ? AND receive_employee = ? AND close_date is null";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnEmployee);
    	String vnSizeData = result.get("SIZE_DATA").toString();
    	return Integer.parseInt(vnSizeData);
    }
    
    public int findExistDataIsAlreadyClosed(int pnSiteId, int pnEmployee) {
    	String vaCurrentDate = JDate.getYear()+JDate.getMonth()+JDate.getDay();
    	String vaSql = "SELECT count(*) as SIZE_DATA FROM rc_trans_m WHERE site_id = ? AND receive_employee = ? AND close_date = ?";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnEmployee, vaCurrentDate);
    	String vnSizeData = result.get("SIZE_DATA").toString();
    	return Integer.parseInt(vnSizeData);
    }

	public void UpdateColseDateInDatabase(int pnSiteId, int pnUserId) {
    	String vaCurrentDate = JDate.getYear()+JDate.getMonth()+JDate.getDay();
        String vaSql = "Update rc_trans_m SET close_date = ? WHERE site_id = ? and receive_employee = ? ";
        try {
        	db.update(vaSql,vaCurrentDate, pnSiteId, pnUserId);
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }			
	}
	
	public void UpdateColseDateIsNull(int pnSiteId, int pnUserId) {
        String vaSql = "Update rc_trans_m SET close_date = null WHERE site_id = ? and receive_employee = ? ";
        try {
        	db.update(vaSql, pnSiteId, pnUserId);
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }			
	}
	
	// ค้นหาข้อมูล RcTransM เพื่อยกเลิกใบเสร็จรับเงิน
 		public Map<String, Object> searchRcTransMForCancel(int pnSiteId, long pnSerialNumber) {
 	    	String vaSql = "SELECT serial_number, NVL(account,' ') AS account, NVL(payer_name,' ') AS payer_name, NVL(payer_address,' '), penalty, close_date, "
 	    			+ "CASE status WHEN 'C' THEN '0' ELSE '1' END AS status "
 	    			+ "FROM rc_trans_m "
 					+ "WHERE site_id = ? AND serial_number = ?";
 	        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnSerialNumber);
 	        if (result != null) {
 	        	return result;
 	        } else {
 	            return null;
 	        }
 	    }
	
	// ยกเลิกข้อมูล RcTransM เลขใบเสร็จรับเงิน
	 	public boolean cancelRcTransM(RcTransM poNewRcTransM, String paSerialNumber) {
	         String vaSql = "UPDATE rc_trans_m SET "
	         		+ "status = ?, cancel_reason = ?, cancel_date = ?, cancel_time = ?, cancel_employee = ?, "
	         		+ "update_user_id = ?, update_date = ?, update_time = ? WHERE site_id = ? AND serial_number = ?";
	         
	         boolean result = false;
	         try {
	         	db.update(vaSql,
	         			poNewRcTransM.getVaStatus(),
	         			poNewRcTransM.getVaCancelReason(),
	         			poNewRcTransM.getVaCancelDate(),
	         			poNewRcTransM.getVaCancelTime(),
	         			poNewRcTransM.getVaCancelEmployee(),
	         			poNewRcTransM.getVnUpdateUserId(),
	         			poNewRcTransM.getVaUpdateDate(),
	         			poNewRcTransM.getVaUpdateTime(),
	         			poNewRcTransM.getVnSiteId(),
	         			Long.parseLong(paSerialNumber)
	         			);
	         	result = true;
	         } catch (Exception e) {
	         	System.out.println(e);
	         	result = false;
	         }
	         return result;
	 	}
	 	
	 	public List<Map<String, Object>> findRcTransMBySerialNumberAndAccount(int pnSiteId, String paSerialNumber, String paAccount, String paStatus, String paSortColumn,
				 int pnStart, int pnEnd) {
	 		if(paSerialNumber.equals("")){
	        	paSerialNumber ="%";
	        }
	        if(paAccount.equals("")){
	        	paAccount ="%";
	        }
	        if(paStatus.equals("1")){
	        	paStatus = "AND rc_trans_m.status IS  NULL ";
	        }
	        if(paStatus.equals("2")){
	        	paStatus = "AND rc_trans_m.status = 'C' OR rc_trans_m.status = 'c'";
	        }
			String vaSql = "SELECT * FROM (SELECT ROWNUM as rnum,account,serial_number,receive_date,receive_time,"
					+ "user_pname||' '||user_fname||' '||user_lname AS name,gross_amount,status "
					+ "FROM(SELECT * FROM rc_trans_m,user_login WHERE "
	        		+ "rc_trans_m.receive_employee = user_login.user_id AND rc_trans_m.site_id = "+pnSiteId+" "
	        		+ "AND (COALESCE(rc_trans_m.serial_number, -1) LIKE '"+paSerialNumber+"') "
	        		+ "AND (COALESCE(rc_trans_m.account, ' ') LIKE '"+paAccount+"') "+paStatus+" ORDER BY "+paSortColumn+")"
	        		+ "WHERE ROWNUM <="+pnEnd+") WHERE rnum >"+pnStart+"";
	        List<Map<String, Object>> result = db.queryList(vaSql);
	        if (result != null) {
	            return result;
	        } else {
	            return null;
	        }
	    }
		
		
		public List<Map<String, Object>> findAllRcTransMBySerialNumberAndAccount(int pnSiteId, String paSerialNumber, String paAccount, String paStatus,String paSortColumn) {
	        if(paSerialNumber.equals("")){
	        	paSerialNumber ="%";
	        }
	        if(paAccount.equals("")){
	        	paAccount ="%";
	        }
	        if(paStatus.equals("1")){
	        	paStatus = "AND rc_trans_m.status IS  NULL ";
	        }
	        if(paStatus.equals("2")){
	        	paStatus = "AND rc_trans_m.status = 'C' OR rc_trans_m.status = 'c'";
	        }
	        String vaSql = "SELECT account,rc_trans_m.serial_number,rc_trans_m.receive_date,rc_trans_m.receive_time,"
	        		+ "user_login.user_pname||' '||user_login.user_fname||' '||user_login.user_lname AS name,"
	        		+ "rc_trans_m.gross_amount,rc_trans_m.status   FROM rc_trans_m,user_login WHERE "
	        		+ "rc_trans_m.receive_employee = user_login.user_id AND rc_trans_m.site_id = "+pnSiteId+" "
	        		+ "AND (COALESCE(rc_trans_m.serial_number, -1) LIKE '"+paSerialNumber+"') "
	        		+ "AND (COALESCE(rc_trans_m.account, ' ') LIKE '"+paAccount+"') "+paStatus+" ORDER BY "+paSortColumn;
	        List<Map<String, Object>> result = db.queryList(vaSql);
	        if (result != null) {
	            return result;
	        } else {
	            return null;
	        }
	    }
		 public int getCountRcTransMBySerialNumberAndAccount(int pnSiteId, String paSerialNumber, String paAccount, String paStatus,String paSortColumn) {
		    	
			 if(paSerialNumber.equals("")){
		        	paSerialNumber ="%";
		        }
		        if(paAccount.equals("")){
		        	paAccount ="%";
		        }
		        if(paStatus.equals("1")){
		        	paStatus = "AND rc_trans_m.status IS  NULL ";
		        }
		        if(paStatus.equals("2")){
		        	paStatus = "AND rc_trans_m.status = 'C' OR rc_trans_m.status = 'c'";
		        }
				String vaSql = "SELECT COUNT(*) FROM(SELECT rc_trans_m.account,rc_trans_m.serial_number,rc_trans_m.receive_date,rc_trans_m.receive_time,"
		        		+ "user_login.user_pname||' '||user_login.user_fname||' '||user_login.user_lname AS name,"
		        		+ "rc_trans_m.gross_amount,rc_trans_m.status FROM rc_trans_m,user_login WHERE "
		        		+ "rc_trans_m.receive_employee = user_login.user_id AND rc_trans_m.site_id = "+pnSiteId+" "
		        		+ "AND (COALESCE(rc_trans_m.serial_number, -1) LIKE '"+paSerialNumber+"') "
		        		+ "AND (COALESCE(rc_trans_m.account, ' ') LIKE '"+paAccount+"') "+paStatus+" ORDER BY "+paSortColumn+")";
		      
		       
		        Map<String, Object> result = db.querySingle(vaSql);
		        if (result != null) {
		        	return Integer.parseInt(result.get("COUNT(*)").toString());
		        } else {
		            return 0;
		        }
		}
		 
		
		 public int getCountAllRcTransMBySerialNumberAndAccount(int pnSiteId, String paSerialNumber, String paAccount, String paStatus,String paSortColumn) {
		    	
			 if(paSerialNumber.equals("")){
		        	paSerialNumber ="%";
		        }
		        if(paAccount.equals("")){
		        	paAccount ="%";
		        }
		        if(paStatus.equals("1")){
		        	paStatus = "AND rc_trans_m.status IS  NULL ";
		        }
		        if(paStatus.equals("2")){
		        	paStatus = "AND rc_trans_m.status = 'C' OR rc_trans_m.status = 'c'";
		        }
				String vaSql = "SELECT COUNT(*) FROM(SELECT account,rc_trans_m.serial_number,rc_trans_m.receive_date,rc_trans_m.receive_time,"
	        		+ "user_login.user_pname||' '||user_login.user_fname||' '||user_login.user_lname AS name,"
	        		+ "rc_trans_m.gross_amount,rc_trans_m.status   FROM rc_trans_m,user_login WHERE "
	        		+ "rc_trans_m.receive_employee = user_login.user_id AND rc_trans_m.site_id = "+pnSiteId+" "
	        		+ "AND (COALESCE(rc_trans_m.serial_number, -1) LIKE '"+paSerialNumber+"') "
	        		+ "AND (COALESCE(rc_trans_m.account, ' ') LIKE '"+paAccount+"') "+paStatus+" ORDER BY "+paSortColumn+")";
		      
		       
		        Map<String, Object> result = db.querySingle(vaSql);
		        if (result != null) {
		        	return Integer.parseInt(result.get("COUNT(*)").toString());
		        } else {
		            return 0;
		        }
		}
		 
		 public List<Map<String, Object>> findRcTransMByBetweenDateAndAccount(int pnSiteId, String paStartDate, String paEndDate, String paAccount, String paStatus, String paSortColumn,
					 int pnStart, int pnEnd) {
			 String date ="";
			 if(!paStartDate.equals("") ){
				 date = "AND rc_trans_m.receive_date >= "+paStartDate+" AND rc_trans_m.receive_date <= "+paEndDate+" ";	
		     }
		        if(paAccount.equals("")){
		        	paAccount ="%";
		        }
		        if(paStatus.equals("1")){
		        	paStatus = "AND rc_trans_m.status IS  NULL ";
		        }
		        if(paStatus.equals("2")){
		        	paStatus = "AND rc_trans_m.status = 'C' OR rc_trans_m.status = 'c'";
		        }
				String vaSql = "SELECT * FROM (SELECT ROWNUM as rnum,account,serial_number,receive_date,receive_time,"
						+ "user_pname||' '||user_fname||' '||user_lname AS name,gross_amount,status "
						+ "FROM(SELECT * FROM rc_trans_m,user_login WHERE "
		        		+ "rc_trans_m.receive_employee = user_login.user_id AND rc_trans_m.site_id = "+pnSiteId+" "
		        		+ date +" "
		        		+ "AND (COALESCE(rc_trans_m.account, ' ') LIKE '"+paAccount+"') "+paStatus+" ORDER BY "+paSortColumn+")"
		        		+ "WHERE ROWNUM <="+pnEnd+") WHERE rnum >"+pnStart+"";
		        List<Map<String, Object>> result = db.queryList(vaSql);
		        if (result != null) {
		            return result;
		        } else {
		            return null;
		        }
		    }
		 public List<Map<String, Object>> findAllRcTransMByBetweenDateAndAccount(int pnSiteId, String paStartDate, String paEndDate, String paAccount, String paStatus,String paSortColumn) {
			 String date ="";
			 if(!paStartDate.equals("") ){
				 date = "AND rc_trans_m.receive_date >= "+paStartDate+" AND rc_trans_m.receive_date <= "+paEndDate+" ";	
		     }
			 if(paAccount.equals("")){
		        	paAccount ="%";
		        }
		        if(paStatus.equals("1")){
		        	paStatus = "AND rc_trans_m.status IS  NULL ";
		        }
		        if(paStatus.equals("2")){
		        	paStatus = "AND rc_trans_m.status = 'C' OR rc_trans_m.status = 'c'";
		        }
		        String vaSql = "SELECT account,rc_trans_m.serial_number,rc_trans_m.receive_date,rc_trans_m.receive_time,"
		        		+ "user_login.user_pname||' '||user_login.user_fname||' '||user_login.user_lname AS name,"
		        		+ "rc_trans_m.gross_amount,rc_trans_m.status   FROM rc_trans_m,user_login WHERE "
		        		+ "rc_trans_m.receive_employee = user_login.user_id AND rc_trans_m.site_id = "+pnSiteId+" "
		        		+ date +" "
		        		+ "AND (COALESCE(rc_trans_m.account, ' ') LIKE '"+paAccount+"') "+paStatus+" ORDER BY "+paSortColumn;
		        List<Map<String, Object>> result = db.queryList(vaSql);
		        if (result != null) {
		            return result;
		        } else {
		            return null;
		        }
		    }
		 public int getCountRcTransMByBetweenDateAndAccount(int pnSiteId, String paStartDate, String paEndDate, String paAccount, String paStatus, String paSortColumn,
				 int pnStart, int pnEnd) {
			 String date ="";
			 if(!paStartDate.equals("")){
				 date = "AND rc_trans_m.receive_date >= "+paStartDate+" AND rc_trans_m.receive_date <= "+paEndDate+" ";	
		     }
			 
			 if(paAccount.equals("")){
		        	paAccount ="%";
		        }
		        if(paStatus.equals("1")){
		        	paStatus = "AND rc_trans_m.status IS  NULL ";
		        }
		        if(paStatus.equals("2")){
		        	paStatus = "AND rc_trans_m.status = 'C' OR rc_trans_m.status = 'c'";
		        }
				String vaSql = "SELECT COUNT(*) FROM(SELECT rc_trans_m.account,rc_trans_m.serial_number,rc_trans_m.receive_date,rc_trans_m.receive_time,"
		        		+ "user_login.user_pname||' '||user_login.user_fname||' '||user_login.user_lname AS name,"
		        		+ "rc_trans_m.gross_amount,rc_trans_m.status FROM rc_trans_m,user_login WHERE "
		        		+ "rc_trans_m.receive_employee = user_login.user_id AND rc_trans_m.site_id = "+pnSiteId+" "
		        		+ date +" "
		        		+ "AND (COALESCE(rc_trans_m.account, ' ') LIKE '"+paAccount+"') "+paStatus+" ORDER BY "+paSortColumn+")";
		      
		       
		        Map<String, Object> result = db.querySingle(vaSql);
		        if (result != null) {
		        	return Integer.parseInt(result.get("COUNT(*)").toString());
		        } else {
		            return 0;
		        }
		}

		
		 public int getCountAllRcTransMByBetweenDateAndAccount(int pnSiteId, String paStartDate, String paEndDate, String paAccount, String paStatus, String paSortColumn) {
			 String date ="";
			 if(!paStartDate.equals("")){
				 date = "AND rc_trans_m.receive_date >= "+paStartDate+" AND rc_trans_m.receive_date <= "+paEndDate+" ";	
		     }	
			 if(paAccount.equals("")){
		        	paAccount ="%";
		        }
		        if(paStatus.equals("1")){
		        	paStatus = "AND rc_trans_m.status IS  NULL ";
		        }
		        if(paStatus.equals("2")){
		        	paStatus = "AND rc_trans_m.status = 'C' OR rc_trans_m.status = 'c'";
		        }
		        String vaSql = "SELECT COUNT(*) FROM(SELECT account,rc_trans_m.serial_number,rc_trans_m.receive_date,rc_trans_m.receive_time,"
		        		+ "user_login.user_pname||' '||user_login.user_fname||' '||user_login.user_lname AS name,"
		        		+ "rc_trans_m.gross_amount,rc_trans_m.status   FROM rc_trans_m,user_login WHERE "
		        		+ "rc_trans_m.receive_employee = user_login.user_id AND rc_trans_m.site_id = "+pnSiteId+" "
		        		+ date +" "
		        		+ "AND (COALESCE(rc_trans_m.account, ' ') LIKE '"+paAccount+"') "+paStatus+" ORDER BY "+paSortColumn+")";
			      
		       
		        Map<String, Object> result = db.querySingle(vaSql);
		        if (result != null) {
		        	return Integer.parseInt(result.get("COUNT(*)").toString());
		        } else {
		            return 0;
		        }
		}
		 
		// บันทึกข้อมูล RcTransM สำหรับรับเงินค่าอื่นๆ
	 	public boolean insertRcTransMForReceiveOther(RcTransM poNewRcTransM) {
	         String vaSql = "INSERT INTO rc_trans_m ( "
	         		 + "site_id, serial_number, account, payer_name, payer_address, receive_date, receive_time, receive_employee, "
	        		 + "net_amount, vat_amount, gross_amount, insert_user_id, insert_date, insert_time, update_user_id) "
	         		 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	         
	         boolean result = false;
	         try {
	         	db.update(vaSql,
	         			poNewRcTransM.getVnSiteId(),
	         			poNewRcTransM.getVnSerialNumber(),
	         			poNewRcTransM.getVaAccount(),
	         			poNewRcTransM.getVaPayerName(),
	         			poNewRcTransM.getVaPayerAddress(),
	         			poNewRcTransM.getVaReceiveDate(),
	         			poNewRcTransM.getVaReceiveTime(),
	         			poNewRcTransM.getVnReceiveEmployee(),
	         			poNewRcTransM.getVnNetAmount(),
	         			poNewRcTransM.getVnVatAmount(),
	         			poNewRcTransM.getVnGrossAmount(),
	         			poNewRcTransM.getVnInsertUserId(),
	         			poNewRcTransM.getVaInsertDate(),
	         			poNewRcTransM.getVaInsertTime(),
	         			poNewRcTransM.getVnUpdateUserId()
	         			);
	         	result = true;
	         } catch (Exception e) {
	         	System.out.println(e);
	         	result = false;
	         }
	         return result;
	 	}
	 	
	 	public boolean deleteRcTransMForCancelRecieveBill(int pnSiteId,String paSereialNumber){
	 		boolean result;
	 		try{
	 		String vaSql = " " +
	 				"DELETE RC_TRANS_M " +
	 				"WHERE SITE_ID = '"+pnSiteId+"' " +
	 				"AND SERIAL_NUMBER = '"+paSereialNumber+"' ";
	 		db.update(vaSql);
	 		result = true;
	 		}
	 		catch (Exception e) {
	 			result = false;
			}
	 		
	 		System.out.println("Satus deleteRcTransM : "+result);
			return result;
	 		
	 	}

}
